using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

namespace OA.Common
{
    /// <summary>
    /// Nhibernate 中的查询对象必须重命名为it
    /// </summary>
    public static class  HqlQeuryHelp
    {
        /// <summary>
        /// 获取指定属性的类型支持多级
        /// </summary>
        /// <param name="model">类型</param>
        /// <param name="propertyName">一级或多级属性名称，以.分隔上下级属性</param>
        /// <returns></returns>
        private static Type GetPropertyType(Type model, string propertyName)
        {
            var type = model;
            var propertys = propertyName.Split('.');
            for (int i = 0; i < propertys.Length; i++)
            {
                type = type.GetProperty(propertys[i]).PropertyType;
            }
            type = type.UnderlyingSystemType;
            if (type.IsGenericType && type.GetGenericTypeDefinition() == typeof(Nullable<>))
            {
                type = type.GetGenericArguments()[0];
            }
            return type;
        }

        /// <summary>
        /// hql 转字符串函数
        /// </summary>
        /// <param name="propertyType">属性类型</param>
        /// <param name="propertyName">名称</param>
        /// <returns></returns>
        private static string ConvertToString(Type propertyType, string propertyName)
        {
            string ret = propertyName;
            if (propertyType == typeof(DateTime))
            {
                //将日期转换为 yyyy/mm/dd hh:mm:ss 格式的字符串
                StringBuilder sb = new StringBuilder();
                sb.Append("(");
                sb.Append("cast(year({0}) as string)");
                sb.Append("+'{1}'+substring('0'+cast(month({0}) as string),length('0'+cast(month({0}) as string))-1,2)");
                sb.Append("+'{1}'+substring('0'+cast(day({0}) as string),length('0'+cast(day({0}) as string))-1,2)");

                sb.Append("+' '+substring('0'+cast(hour({0}) as string),length('0'+cast(hour({0}) as string))-1,2)");
                sb.Append("+'{2}'+substring('0'+cast(minute({0}) as string),length('0'+cast(minute({0}) as string))-1,2)");
                sb.Append("+'{2}'+substring('0'+cast(second({0}) as string),length('0'+cast(second({0}) as string))-1,2)");
                sb.Append(")");
                ret = string.Format(sb.ToString(), propertyName, "/", ":");
            }
            else if (propertyType != typeof(string) && propertyType != typeof(char))
            {
                ret = string.Format("cast({0} as string)", propertyName);
            }
            return ret;
        }


        #region 构建查询条件

        /// <summary>
        /// 获取查询条件
        /// </summary>
        /// <param name="model">模型</param>
        /// <param name="keys">属性名称，将使用这些属性名去表单中查询</param>
        /// <returns></returns>
        public static string GetWhere(Type model, string strkeys)
        {
            string ret = null;
            StringBuilder sb = new StringBuilder();
            List<string> values = new List<string>();
            var keys = strkeys.Split(',');
            foreach (var key in keys)
            {
                var value = Common.Utils.SqlInject(DTRequest.GetString(key));
                if (!string.IsNullOrEmpty(value))
                {
                    sb.Append(key + ",");
                    values.Add(value);
                }
            }
            if (sb.Length > 0)
            {
                sb.Remove(sb.Length - 1, 1);
                ret = GetWhere(model, sb.ToString(), values.ToArray());
            }
            return ret;
        }

        public static string GetWhere(Type model, string strkeys, params string[] values)
        {
            const string and = " and ";
            var keys = strkeys.Split(',');
            StringBuilder sb = new StringBuilder();
            for (int i = 0; i < keys.Length; i++)
            {
                var propertyType = GetPropertyType(model, keys[i]);
                var hql = BuilderPropertyHqlWhere(propertyType, "it." + keys[i], values[i]);  //有括号的Nhibernate 必须加前缀
                if (!string.IsNullOrEmpty(hql))
                {
                    sb.Append("(" + hql + ")");
                    sb.Append(and);
                }
            }
            if (sb.Length > 0)
            {
                sb.Remove(sb.Length - and.Length, and.Length);
            }
            return sb.ToString();
        }



        private static string BuilderPropertyHqlWhere(Type propertyType, string propertyName, string where)
        {
            StringBuilder ret = new StringBuilder();
            string[] wheres = where.Split(new string[] { "|" }, StringSplitOptions.RemoveEmptyEntries);
            if (wheres.Length > 0)
            {
                for (int i = 0; i < wheres.Length; i++)
                {
                    ret.Append(ToHql(propertyType, propertyName, wheres[i]));
                    if (i < wheres.Length - 1) ret.Append(" or ");
                }
            }
            return ret.ToString();
        }

        private static string ToHql(Type propertyType, string propertyName, string where)
        {
            string ret;
            ret = NullExpression(propertyName, where);
            if (!string.IsNullOrEmpty(ret)) return ret;

            if (!where.StartsWith("<>"))
            {
                ret = OperExpression(propertyType, propertyName, where);
                if (!string.IsNullOrEmpty(ret)) return ret;
            }

            ret = BetweenExpression(propertyType, propertyName, where);
            if (!string.IsNullOrEmpty(ret)) return ret;

            ret = LikeExpression(propertyType, propertyName, where);
            return ret;
        }

        #region 各种表达式

        private static string NullExpression(string propertyName, string where)
        {
            StringBuilder sb = new StringBuilder();
            if (where == "is null" || where == "is not null")
            {
                sb.Append(propertyName + " " + where);
            }
            return sb.ToString();
        }


        private static string OperExpression(Type propertyType, string propertyName, string where)
        {
            StringBuilder sb = new StringBuilder();
            string[] optypes = { "=", "<>", ">=", "<=", "<", ">" };
            int j;
            for (j = 0; j < optypes.Length; j++)
            {
                var optype = optypes[j];
                if (where.StartsWith(optype))
                {
                    var value = where.Substring(optype.Length);
                    if (!(Common.Utils.IsNumberType(propertyType) && Common.Utils.IsNumeric(value)))
                    {
                        propertyName = ConvertToString(propertyType, propertyName);
                        value = "'" + value + "'";
                    }
                    sb.Append(propertyName + optype + value);
                    break;
                }

            }
            return sb.ToString();
        }


        private static string BetweenExpression(Type propertyType, string propertyName, string where)
        {
            string ret = null;
            var whereitems = where.Split(new string[] { ":" }, StringSplitOptions.RemoveEmptyEntries);
            if (whereitems.Length == 2)   //有且仅只有一个：号时，有多个时认为不合法，不组成between语句
            {
                var left = whereitems[0];
                var right = whereitems[1];
                if (!(Common.Utils.IsNumberType(propertyType) && Common.Utils.IsNumeric(left) && Common.Utils.IsNumeric(right)))
                {
                    propertyName = ConvertToString(propertyType, propertyName);
                    left = "'" + left + "'";
                    right = "'" + right + "'";
                }
                ret = string.Format("{0} between {1} and {2}", propertyName, left, right);
            }
            return ret;
        }


        private static string LikeExpression(Type propertyType, string propertyName, string where)
        {
            string op = "<>";
            string value;
            if (where.StartsWith(op))
            {
                value = where.Substring(op.Length);
            }
            else
            {
                value = where;
                op = "=";
            }

            if (value.IndexOf("*") >= 0 || value.IndexOf("?") >= 0)
            {
                var like = (op == "<>" ? " not" : "") + " like ";
                if (value.IndexOf("%") >= 0 || value.IndexOf("_") >= 0)
                {
                    value = value.Replace(@"\", @"\\").Replace("%", @"\%").Replace("_", @"\_").Replace("[", @"\[");
                    value = value + @"' escape '\";
                }
                value = value.Replace("*", "%").Replace("?", "_");
                propertyName = ConvertToString(propertyType, propertyName);
                value = propertyName + like + "'" + value + "'";
            }
            else
            {
                value = OperExpression(propertyType, propertyName, op + value);
            }
            return value;
        }

        #endregion

        #endregion
    }
}
